#!/usr/bin/perl

use warnings;
use strict;

use feature qw/say/;
use Spreadsheet::ParseExcel;
use Spreadsheet::XLSX;

use Carp qw(confess  cluck);
use Cwd 'abs_path';
use File::Basename;
use File::Path qw(make_path);
use FindBin qw($RealBin $RealScript);
use Getopt::Long;
use Term::ANSIColor;
use Text::Table;
use Data::Printer {indent         => 1, hash_separator => ' '};

use lib $RealBin;
#use Data::Printer {indent         => 1, hash_separator => ' ', index => 0};
use Carp::Always::Color;
use CON;
use FlHandle;
use ZipSlurp;


$|=1;

sub print_usage
{
    my $strU0 = <<"EOU";
  **USAGE**
$RealBin/$RealScript  -spread <xls-file1> <xlsx-file2>  -out <dir> 
-spread         # Required: Full path to spreadsheet file                                                       
-out            # optional: output directory                                                                    
-format <value> # optional: default is each cell's unformatted string; -format value would get the formatted one


EOU

    say $strU0;


}

my $cell_format = q/unformatted/;
my $OutDir;
my @AxlsFs;

GetOptions(
    'format=s'         => \$cell_format,
    "spreadsheet=s{,}" => \@AxlsFs,
    'out_dir=s'        => \$OutDir,
);

if(not @AxlsFs )  { 
    print_usage();
    Carp::confess "-E-no -spreadsheet file.xls or file.xlsx provided.";
}

map {
    if( not -e $_) { Carp::confess "\n*****-E-  spreadsheet file $_ not exists    *****\n"; }
} @AxlsFs;

if($OutDir  and (not -d $OutDir)) { 
    &make_path ($OutDir) or Carp::confess "cannot mkdir $OutDir";
}
if (not $OutDir){ $OutDir = "./"; }
$OutDir = abs_path($OutDir);


my @absF;
foreach my $f3 (@AxlsFs){#_{{_
    my %H  =();
    my $abs_file_spreadsheet = abs_path($f3);
    if (! $abs_file_spreadsheet) { $abs_file_spreadsheet = Cwd::fast_abs_path($f3); }
    my ($excbase,$excDir,$suffix) = fileparse($abs_file_spreadsheet, qr/\.[^.]*(\.gz)?/);
    my $excel;
    if ($suffix =~m#xlsx#i){
        printf "reading xlsx %8s  file \n", &CONm($abs_file_spreadsheet);
        $excel  =Spreadsheet::XLSX -> new($abs_file_spreadsheet);
    } elsif($suffix =~m#xls#i){
        printf "reading xls %8s  file \n", &CONm($abs_file_spreadsheet, 2);
        my $oExcel = new Spreadsheet::ParseExcel;
        $excel = $oExcel->Parse($abs_file_spreadsheet);
    } else {
        Carp::cluck "-W- please provide xls or xlsx file; $abs_file_spreadsheet not so";
        next;
    }
    my $st = np $excel;
    my $postfix = &FlHandle::GetRndHex;

    my $ct = 0;
    while (-e "$OutDir/$excbase.$ct.$postfix" ){ $ct +=1;}


    my $fn = qq($OutDir/$excbase.$ct.$postfix);
    my $stro = q//;

    foreach my $sheet ( $excel->worksheets() ) {
        my $SheetName = $sheet->get_name();
        $H{$SheetName} = &SheetEntries($sheet);
    }
    $st = np %H;
    $stro .= "\n\n";
    $stro .= $st;

    &ZipSlurp::writeZF(\$stro, $fn ) or Carp::confess "-E- cannot write to $fn";
    push @absF, "$OutDir/$excbase.$ct.$postfix";

}#_}}_

if ($#absF == 1 ){
    my $cmd = "/usr/bin/xxdiff  $absF[0] $absF[1] & ";
    printf "running %8s  \n", &CONm($cmd, 3);
    system $cmd;
}
elsif ($#absF == 2 ){
    my $cmd = "/usr/bin/xxdiff $absF[0] $absF[1] $absF[2] & ";
    printf "running %8s  \n", &CONm($cmd, 4);
    system $cmd;
}
#p @absF;

#######################################################################
#                            sub routines                             #
#######################################################################

sub SheetEntries
{#_{{_
    my ($Sh) = @_;

    my ( $row_min, $row_max ) = $Sh->row_range();
    my ( $col_min, $col_max ) = $Sh->col_range();


    my $tb = Text::Table->new();
    foreach my $row ( $row_min .. $row_max ) {
        my @At2 = ("-");
        foreach my $col ( $col_min .. $col_max ) {
            my $cell = $Sh->get_cell( $row, $col );
            my $str0 = "";
            if ( defined $cell ) {
                if ( $cell_format =~ m#value#i ) {
                    $str0 = $cell->value();
                } else {
                    $str0 = $cell->unformatted();
                }
                $str0 =~ s#[\r]##g;
            } else {
            }
            push @At2, $str0;
        }
        $tb->add(@At2);

    }
    my $str2 = $tb->stringify();
    $str2 = "\n\n".$str2;
    return $str2;


}#_}}_


